package com.read;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadXl {

	/**
	 * @param args
	 */
	
	public static final String s1 = "INSERT INTO  promotion_details  ( promo_id ,  cust_id ,  product_id ,  start_date ,  end_date ,    discount ,  planed_volume ,  revenue ) VALUES ( ";
	
	public static final String s2 = "INSERT INTO  sales_order  ( id,sales_order_id ,  cust_id ,  product_id ,  sales_date ,  quantity ,  discount ,  net_price ) VALUES( "; 
	
	private static void generateSO(){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			Workbook workbook = Workbook.getWorkbook(new File("C:\\Swastik\\TEST\\sales.xls"));
			Sheet sheet = workbook.getSheet(0);
			 Writer output = null;
			
			  File file = new File("C:\\Swastik\\TEST\\sales.sql");
			  output = new BufferedWriter(new FileWriter(file));
			 
			
			
			int promotionId ;
			int custId;
			int producatId;
			Date stdate;
			
			String string1 ; 
			String start;
			int volume;
			double discount;
			int id;
			double revenue;
			for(int i=1 ; i< 501 ; i++){
				
					Cell a1 = sheet.getCell(0,i);
					Cell a2 = sheet.getCell(1,i);
					Cell a3 = sheet.getCell(2,i);
					Cell a4 = sheet.getCell(3,i);
					Cell a5 = sheet.getCell(4,i);
					Cell a6 = sheet.getCell(5,i);
					Cell a7 = sheet.getCell(6,i);
					Cell a8 = sheet.getCell(7,i);
					
					NumberCell nc = (NumberCell) a1; 
					promotionId = (int) nc.getValue(); 
					nc = (NumberCell) a2; 
					custId = (int) nc.getValue(); 
					nc = (NumberCell) a3; 
					producatId = (int) nc.getValue(); 
					DateCell dc = (DateCell) a4; 
					stdate = dc.getDate(); 
					start = df.format(stdate);
					nc = (NumberCell) a5; 
					volume =  (int) nc.getValue();
					nc = (NumberCell) a6; 
					discount =  nc.getValue();
					
					nc = (NumberCell) a7; 
					revenue =  nc.getValue();
					nc = (NumberCell) a8; 
					id =  (int) nc.getValue();
					
					
					String text1 = s2 +id + "," +promotionId + "," + custId +","+producatId+","+"'"+start+"'"+","+volume+","+discount+","+revenue+");";
					 output.write(text1);
					 output.write("\n");
					  
					System.out.println(s2 +promotionId + "," + custId +","+producatId+","+"'"+start+"'"+","+volume+","+discount+","+revenue+");");
			}
			output.close();
		}catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void generatePromo(){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			Workbook workbook = Workbook.getWorkbook(new File("C:\\Swastik\\TEST\\Marketing Dashboard.xls"));
			Sheet sheet = workbook.getSheet(0);
			 Writer output = null;
			 
			  File file = new File("C:\\Swastik\\TEST\\promo.sql");
			  output = new BufferedWriter(new FileWriter(file));
			 
			
			
			  int promotionId ;
				int custId;
				int producatId;
				Date stdate;
				Date endate;
				String string1 ; 
				String start;
				String endDate;
				double discount;
				int volume;
				double revenue;
				
			for(int i=200 ; i< 300 ; i++){
				Cell a1 = sheet.getCell(0,i);
				Cell a2 = sheet.getCell(1,i);
				Cell a3 = sheet.getCell(2,i);
				Cell a4 = sheet.getCell(3,i);
				Cell a5 = sheet.getCell(4,i);
				Cell a6 = sheet.getCell(5,i);
				Cell a7 = sheet.getCell(6,i);
				Cell a8 = sheet.getCell(7,i);
				
				NumberCell nc = (NumberCell) a1; 
				promotionId = (int) nc.getValue(); 
				nc = (NumberCell) a2; 
				custId = (int) nc.getValue(); 
				nc = (NumberCell) a3; 
				producatId = (int) nc.getValue(); 
				DateCell dc = (DateCell) a4; 
				stdate = dc.getDate(); 
				start = df.format(stdate);
				dc = (DateCell) a5; 
				endate = dc.getDate(); 
				endDate = df.format(endate);
				nc = (NumberCell) a6; 
				discount =  nc.getValue();
				nc = (NumberCell) a7; 
				volume =  (int) nc.getValue();
				nc = (NumberCell) a8; 
				revenue =  nc.getValue();
				
					String text1 = s1 +promotionId + "," + custId +","+producatId+","+"'"+start+"'"+","+"'"+endDate+"'"+","+discount+","+volume+","+revenue+");";
					 output.write(text1);
					 output.write("\n");
					  
					System.out.println(s1 +promotionId + "," + custId +","+producatId+","+"'"+start+"'"+","+volume+","+discount+","+revenue+");");
			}
			output.close();
			
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public static void main(String[] args) {
		generateSO();
		generatePromo();
	}
	
}
